package grapid.database;

import grapid.Grapid;

import java.util.ArrayList;
import java.util.List;
import java.util.TreeMap;

/**
 * SQL语句管理类<br />
 * 以PreparedStatement方式组建sql
 * 
 * @author grapid
 * @version 2.0
 * 
 */
public class GQueryP {

	/**
	 * 数据库类型
	 */
	private Integer dbtype = Grapid.DEFAULT_DBTYPE;
	/**
	 * 工作模式
	 */
	private int mode = Grapid.DEFAULT_GQUERY_MODE;

	/**
	 * 字段名
	 */
	private String fields;
	/**
	 * 表名
	 */
	private String tableName;
	/**
	 * join语句
	 */
	private String joinSQL;
	/**
	 * 条件语句
	 */
	private String whereSQL;
	/**
	 * 条件语句
	 */
	private List<CQueryPWhereSQLBean> whereSQLP = new ArrayList<CQueryPWhereSQLBean>();
	/**
	 * 排序语句
	 */
	private String orderSQL;
	/**
	 * 分组语句
	 */
	private String groupSQL;
	/**
	 * 插入/更新数据
	 */
	private TreeMap<String, Object> data = new TreeMap<String, Object>();

	/**
	 * 数据缓存
	 */
	private List<Object> sqlDataCache = new ArrayList<Object>();

	/**
	 * 构造函数
	 */
	public GQueryP() {
	}

	/**
	 * 构造函数
	 * 
	 * @param tableName
	 *            表名
	 */
	public GQueryP(String tableName) {
		this.tableName = tableName;
	}

	/**
	 * 构造函数
	 * 
	 * @param tableName
	 *            表名
	 * @param dbtype
	 *            数据库类型
	 * @param mode
	 *            CQuery工作模式
	 */
	public GQueryP(String tableName, Integer dbtype, int mode) {
		this.tableName = tableName;
		this.dbtype = dbtype;
		this.mode = mode;
	}

	/**
	 * 添加插入/更新数据
	 * 
	 * @param columnName
	 *            列名
	 * @param value
	 *            值
	 */
	public void addData(String columnName, Object value) {
		this.data.put(columnName, value);
	}

	/**
	 * 删除插入/更新数据
	 * 
	 * @param columnName
	 *            列名
	 */
	public void removeData(String columnName) {
		this.data.remove(columnName);
	}

	/**
	 * 添加join语句
	 * 
	 * @param joinSQL
	 *            join语句
	 */
	public void addJoinSQL(String joinSQL) {
		if (this.joinSQL == null)
			this.joinSQL = "";
		this.joinSQL += " " + joinSQL;
	}

	/**
	 * 添加where语句
	 * 
	 * @param whereSQL
	 *            条件语句
	 */
	public void addWhereSQL(String whereSQL) {
		if (this.whereSQL == null) {
			this.whereSQL = "";
		} else {
			this.whereSQL += " AND ";
		}
		this.whereSQL += whereSQL;
	}

	public void addWhereSQL(String columnName, String relation, Object value) {
		CQueryPWhereSQLBean bean = new CQueryPWhereSQLBean();
		bean.setColumnName(columnName);
		bean.setRelation(relation);
		bean.setValue(value);
		this.whereSQLP.add(bean);
	}

	/**
	 * 过滤
	 * 
	 * @param sql
	 * @return 返回过滤后的sql
	 */
	public String filter(String sql) {
		if ((this.mode & Grapid.GQuery_MODE_DATA_FILTER) == 0) {
			if (Grapid.DB_MYSQL.equals(this.dbtype))
				return sql.replaceAll("'", "''").replaceAll("\\\\", "\\\\\\\\");
			else
				return sql.replaceAll("'", "''");
		}
		return sql;
	}

	/**
	 * 取SQL语句 - 插入
	 * 
	 * @return 返回SQL插入语句
	 */
	public String getSQLInsert() {
		String sql1 = "";
		String sql2 = "";
		for (String key : this.data.keySet()) {
			Object value = this.data.get(key);
			sql1 += ", " + key;
			sql2 += ", ?";
			this.sqlDataCache.add(value);
		}
		String sql = "".equals(sql1) ? "" : "INSERT INTO " + this.tableName + " (" + sql1.substring(1) + ")VALUES("
				+ sql2.substring(1) + ")";
		debug(sql);
		return sql;
	}

	/**
	 * 取SQL语句 - 新插入数据id
	 * 
	 * @return 返回SQL取新插入数据id语句
	 */
	public String getSQLInsertId() {
		if (Grapid.DB_MYSQL.equals(this.dbtype)) {
			// select last_insert_id() id
			return "SELECT @@session.IDENTITY AS id";
		} else if (Grapid.DB_MSSQL.equals(this.dbtype)) {
			return "SELECT @@IDENTITY AS id";
		} else if (Grapid.DB_SQLITE.equals(this.dbtype)) {
			// "SELECT seq FROM sqlite_sequence WHERE (name = '" + this.tableName + "')"
			return "select last_insert_rowid() id";
		}
		return null;
	}

	/**
	 * 取SQL语句 - 删除
	 * 
	 * @return 返回SQL删除语句
	 */
	public String getSQLDelete() {
		String sql = "DELETE FROM " + this.tableName + makeSQLWhere();
		debug(sql);
		return sql;
	}

	/**
	 * 取SQL语句 - 更新
	 * 
	 * @return 返回SQL更新语句
	 */
	public String getSQLUpdate() {
		String sql = "";
		for (String key : this.data.keySet()) {
			Object value = this.data.get(key);
			if (value == null)
				sql += key + " = null";
			else {
				sql += key + " = ?";
				this.sqlDataCache.add(value);
			}
		}
		sql = "".equals(sql) ? "" : "UPDATE " + this.tableName + " SET " + sql.substring(1) + makeSQLWhere();
		debug(sql);
		return sql;
	}

	/**
	 * 取SQL语句 - 总数
	 * 
	 * @return 返回SQL取总数语句
	 */
	public String getSQLCount() {
		String sql = "SELECT COUNT(0) AS num FROM " + this.tableName;
		if (this.joinSQL != null)
			sql += this.joinSQL;
		sql += makeSQLWhere();
		debug(sql);
		return sql;
	}

	/**
	 * 取SQL语句 - 查询
	 * 
	 * @return 返回SQL查询语句
	 */
	public String getSQLSelect() {
		String sql = "SELECT " + makeSQLSelect();
		debug(sql);
		return sql;
	}

	/**
	 * 取SQL语句 - 查询前num条
	 * 
	 * @param num
	 *            查询数量
	 * @return 返回SQL查询语句
	 */
	public String getSQLSelect(int num) {
		String sql = "";
		if (Grapid.DB_MYSQL.equals(this.dbtype))
			sql = "SELECT " + makeSQLSelect() + " LIMIT " + num;
		else if (Grapid.DB_MSSQL.equals(this.dbtype))
			sql = "SELECT TOP " + num + makeSQLSelect();
		else if (Grapid.DB_SQLITE.equals(this.dbtype))
			sql = "SELECT " + makeSQLSelect() + " LIMIT " + num;
		debug(sql);
		return sql;
	}

	/**
	 * 取SQL语句 - 翻页查询
	 * 
	 * @param pageNo
	 *            页码
	 * @param pageSize
	 *            每页数量
	 * @return 返回SQL翻页查询语句
	 */
	public String getSQLPage(int pageNo, int pageSize) {
		String sql = "";
		int end = pageNo * pageSize;
		int start = end - pageSize;
		if (Grapid.DB_MYSQL.equals(this.dbtype)) {
			sql = "SELECT " + makeSQLSelect() + " LIMIT " + start + "," + pageSize;
		} else if (Grapid.DB_MSSQL.equals(this.dbtype)) {
			sql = "SELECT * FROM (SELECT row_number() ";
			if (this.orderSQL != null)
				sql += " OVER(ORDER BY " + this.orderSQL + ")";
			sql += " AS rownum," + (this.fields == null ? "*" : this.fields) + " FROM " + this.tableName;
			if (this.joinSQL != null)
				sql += this.joinSQL;
			sql += makeSQLWhere();
			if (this.groupSQL != null)
				sql += " GROUP BY " + this.groupSQL;
			sql += ") AS " + this.tableName + " WHERE rownum>" + start + " AND rownum<=" + end;
		} else if (Grapid.DB_SQLITE.equals(this.dbtype)) {
			sql = "SELECT " + makeSQLSelect() + " LIMIT " + start + "," + pageSize;
		}
		debug(sql);
		return sql;
	}

	private String makeSQLSelect() {
		String sql = " " + (this.fields == null ? "*" : this.fields) + " FROM " + this.tableName;
		if (this.joinSQL != null)
			sql += this.joinSQL;
		sql += makeSQLWhere();
		if (this.groupSQL != null)
			sql += " GROUP BY " + this.groupSQL;
		if (this.orderSQL != null)
			sql += " ORDER BY " + this.orderSQL;
		return sql;
	}

	private String makeSQLWhere() {
		String sql = this.whereSQL == null ? "" : this.whereSQL;
		for (CQueryPWhereSQLBean bean : this.whereSQLP) {
			Object value = bean.getValue();
			if ((this.mode & Grapid.GQuery_MODE_CONDITION_NULL) == 0 || value != null) {
				if (!"".equals(sql))
					sql += " AND ";
				if (value == null)
					sql += bean.getColumnName() + " is null";
				else if ("like".equals(bean.getRelation()))
					sql += bean.getColumnName() + " like '" + filter(value.toString()) + "' ";
				else if ("in".equals(bean.getRelation()))
					sql += bean.getColumnName() + " in (" + value.toString() + ") ";
				else {
					sql += bean.getColumnName() + " " + bean.getRelation() + " ?";
					this.sqlDataCache.add(value);
				}
			}
		}
		return "".equals(sql) ? "" : " WHERE " + sql;
	}

	private void debug(String sql) {
		if (!"".equals(sql) && (this.mode & Grapid.GQuery_MODE_DEBUG) != 0) {
			String sqlp = "";
			int index = 1;
			for (Object o : this.sqlDataCache) {
				sqlp += "\t " + index + " : " + o + "\n";
				index++;
			}
			System.out.println(sql + (index > 1 ? "\n" + sqlp : ""));
		}
	}

	/**
	 * 获取数据库类型
	 * 
	 * @return 返回数据库类型
	 */
	public Integer getDbtype() {
		return dbtype;
	}

	/**
	 * 设置数据库类型
	 * 
	 * @param dbtype
	 *            数据库类型
	 */
	public void setDbtype(Integer dbtype) {
		this.dbtype = dbtype;
	}

	/**
	 * 获取CQuery工作模式
	 * 
	 * @return 返回CQuery工作模式
	 */
	public int getMode() {
		return mode;
	}

	/**
	 * 设置CQuery工作模式
	 * 
	 * @param mode
	 *            CQuery工作模式
	 */
	public void setMode(int mode) {
		this.mode = mode;
	}

	/**
	 * 获取CQuery查询字段
	 * 
	 * @return 返回CQuery查询字段
	 */
	public String getFields() {
		return fields;
	}

	/**
	 * 设置CQuery查询字段
	 * 
	 * @param fields
	 *            CQuery查询字段
	 */
	public void setFields(String fields) {
		this.fields = fields;
	}

	/**
	 * 获取CQuery查询表名
	 * 
	 * @return 返回CQuery查询表名
	 */
	public String getTableName() {
		return tableName;
	}

	/**
	 * 设置CQuery查询表名
	 * 
	 * @param tableName
	 *            CQuery查询表名
	 */
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	/**
	 * 获取CQuery join语句
	 * 
	 * @return 返回CQuery join语句
	 */
	public String getJoinSQL() {
		return joinSQL;
	}

	/**
	 * 设置CQuery join语句
	 * 
	 * @param joinSQL
	 *            CQuery join语句
	 */
	public void setJoinSQL(String joinSQL) {
		this.joinSQL = joinSQL;
	}

	/**
	 * 获取CQuery条件语句
	 * 
	 * @return 返回CQuery条件语句
	 */
	public String getWhereSQL() {
		return whereSQL;
	}

	/**
	 * 设置CQuery条件语句
	 * 
	 * @param whereSQL
	 *            CQuery条件语句
	 */
	public void setWhereSQL(String whereSQL) {
		this.whereSQL = whereSQL;
	}

	/**
	 * 获取CQuery排序语句
	 * 
	 * @return 返回CQuery排序语句
	 */
	public String getOrderSQL() {
		return orderSQL;
	}

	/**
	 * 设置CQuery排序语句
	 * 
	 * @param orderSQL
	 *            CQuery排序语句
	 */
	public void setOrderSQL(String orderSQL) {
		this.orderSQL = orderSQL;
	}

	/**
	 * 获取CQuery分组语句
	 * 
	 * @return 返回CQuery分组语句
	 */
	public String getGroupSQL() {
		return groupSQL;
	}

	/**
	 * 设置CQuery分组语句
	 * 
	 * @param groupSQL
	 *            CQuery分组语句
	 */
	public void setGroupSQL(String groupSQL) {
		this.groupSQL = groupSQL;
	}

	/**
	 * 获取CQuery插入/更新数据
	 * 
	 * @return 返回CQuery插入/更新数据
	 */
	public TreeMap<String, Object> getData() {
		return data;
	}

	/**
	 * 设置CQuery插入/更新数据
	 * 
	 * @param data
	 *            CQuery插入/更新数据
	 */
	public void setData(TreeMap<String, Object> data) {
		this.data = data;
	}

	/**
	 * 获取CQueryP条件
	 * 
	 * @return 返回CQueryP条件
	 */
	public List<CQueryPWhereSQLBean> getWhereSQLP() {
		return whereSQLP;
	}

	/**
	 * 设置CQueryP条件
	 * 
	 * @param whereSQLP
	 *            CQueryP条件
	 */
	public void setWhereSQLP(List<CQueryPWhereSQLBean> whereSQLP) {
		this.whereSQLP = whereSQLP;
	}

	/**
	 * 获取sql数据缓存
	 * 
	 * @return 返回sql数据缓存
	 */
	public List<Object> getSqlDataCache() {
		return sqlDataCache;
	}

	/**
	 * 设置sql数据缓存
	 * 
	 * @param sqlDataCache
	 *            sql数据缓存
	 */
	public void setSqlDataCache(List<Object> sqlDataCache) {
		this.sqlDataCache = sqlDataCache;
	}

	/**
	 * 条件语句Bean
	 * 
	 * @author grapid
	 * 
	 */
	public class CQueryPWhereSQLBean {
		private String columnName;
		private String relation;
		private Object value;

		public String getColumnName() {
			return columnName;
		}

		public void setColumnName(String columnName) {
			this.columnName = columnName;
		}

		public String getRelation() {
			return relation;
		}

		public void setRelation(String relation) {
			this.relation = relation;
		}

		public Object getValue() {
			return value;
		}

		public void setValue(Object value) {
			this.value = value;
		}
	}
}
